import pandas as pd
import numpy as np
import re
import wx


def get_path(wildcard):
    app = wx.App(None)
    style = wx.FD_OPEN | wx.FD_FILE_MUST_EXIST
    dialog = wx.FileDialog(None, '请选择Excel信息文件',
                           wildcard=wildcard, style=style)
    if dialog.ShowModal() == wx.ID_OK:
        path = dialog.GetPath()
    else:
        path = None
    dialog.Destroy()
    return path


if __name__ == '__main__':
    io = get_path("EXCEL file(*.xlsx;*.xls)|*.xlsx;*.xls")
    print(io)

    print("你已经选择了"+io+"\n")
    # print("请输入选择的sheet name:\n")
    sheetName = input("请输入选择的sheet name:\n")
    starLine = input("请输入数据开始的行：")
    endLine = input("请输入数据结束的行：")
    starLine = int(starLine)
    endLine = int(endLine)
    outFileName = input("请输入输出文件名：")

    # df_xlsx = pd.read_excel(r'.\data\网点ERP收入202107(1).xlsx','累计', header=None)
    df_xlsx = pd.read_excel(io, sheetName, header=None)
    df_hs = pd.DataFrame()
    df_all = pd.DataFrame()

    # df_all = df_xlsx.iloc[6:4940,0:5]
    df_all = df_xlsx.iloc[starLine:endLine, 0:5]

    df_all[0] = df_all[0].ffill()
    df_all[1] = df_all[1].ffill()

    # 匹配到所有衡水的  机构号以053   253 开头
    df_hs = df_all.loc[df_all[0].apply(
        lambda x: True if re.match('^053|^253', x) else False), :]

    # 去掉 含 营业所 部 机要室 收订班  零售班  的机构 剩下135个网点
    df_hs_end = df_hs.loc[df_hs[1].apply(lambda x: False if x.endswith(
        ('营业所', '部', '机要室', '收订班', '零售班')) else True), :]

    df_hs_data = pd.DataFrame(df_hs_end, columns=[
                              '机构编码', '机构名称', '事业部标识', '收入', '同比', '去年收入', '今年总收入', '去年总收入', '总同比'])
    df_hs_data_end = pd.DataFrame(columns=['今年总收入', '去年总收入', '总同比'])

    df_hs_data['机构编码'] = df_hs_end[0]
    df_hs_data['机构名称'] = df_hs_end[1]
    df_hs_data['事业部标识'] = df_hs_end[2]
    df_hs_data['收入'] = df_hs_end[3]
    df_hs_data['同比'] = df_hs_end[4]

    def oldYear(a, b):
        if np.isnan(b):
            return 0
        else:
            return round(a/(1+b))

    def zongTongBi(a, b):
        if b == 0:
            return 1

        return round((a/b-1), 4)

    df_hs_data['去年收入'] = df_hs_data.apply(
        lambda x: oldYear(x['收入'], x['同比']), axis=1)
    df_hs_data_end['今年总收入'] = df_hs_data.groupby(['机构编码', '机构名称'])['收入'].sum()
    df_hs_data_end['去年总收入'] = df_hs_data.groupby(['机构编码', '机构名称'])[
        '去年收入'].sum()
    df_hs_data_end['总同比'] = df_hs_data_end.apply(
        lambda x: zongTongBi(x['今年总收入'], x['去年总收入']), axis=1)

    # print(df_hs_data_end)

    # df_hs_data_end.to_excel(r'.\data\hs_end_data.xlsx', sheet_name='Sheet1')
    df_hs_data_end.to_excel(outFileName, sheet_name='Sheet1')
